package com.kcs.fxmargin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

import org.apache.log4j.Logger;

import com.kcs.common.utils.Constant;
import com.kcs.fxmargin.bo.SetupRoleProfileBO;
import com.kcs.fxmargin.model.MRole;
import com.kcs.fxmargin.model.MUsersAuth;

public class SetupRoleProfileDAO {
	private Logger log = Logger.getLogger(Constant.LOG_FX);
	public SetupRoleProfileBO getFunctionList(SetupRoleProfileBO bo, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		List<SetupRoleProfileBO> resultList = null;
		SetupRoleProfileBO srp = null;
		
		try{
			sql = "select m.id menu_id"
			     +" , m.name menu_name"
				 +" , a.id auth_id"
			     +" , a.flag auth_flag"
				 +" , a.role_id, r.code"
			     +" , m.seq_show  ,a.row_version "
				 +" from m_menu m "
				 +" left outer join m_users_auth a on m.id=a.menu_id and a.active_status='A'"
				 +" left outer join m_role r on a.role_id=r.id and r.active_status='A'"
				 +" where m.active_status='A'"
				 +" and m.parent_menu is not null"
				 +" order by m.seq_show ";
			
			System.out.println(sql);
			
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			if(rs != null){
				resultList = new ArrayList<SetupRoleProfileBO>();
				
				while(rs.next()){
					srp = new SetupRoleProfileBO();
					srp.setMenuId(rs.getString("menu_id"));
					srp.setMenuName(rs.getString("menu_name"));
					srp.setAuthId(rs.getString("auth_id"));
					srp.setAuthFlag(rs.getString("auth_flag"));
					srp.setRoleId(rs.getString("role_id"));
					srp.setCode(rs.getString("code"));
					srp.setSeqShow(rs.getString("seq_show"));
					srp.setRowVersion(rs.getInt("row_version"));
					
					resultList.add(srp);
				}
				
				Collections.sort(resultList, new SeqShowComparator());

				
				
				bo.setFunctionList(resultList);
			}
			
			
		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return bo;
	}
	
	public int updateRoleProfile(SetupRoleProfileBO bo,Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		SetupRoleProfileBO srp = null;
		String sql = null;
		String updateBy = bo.getUpdateBy();
		int res = -1;
		try{
			
			for(int i=0; i<bo.getFunctionList().size(); i++){
				srp = bo.getFunctionList().get(i);
				
				if(srp.getAuthId() != null){
					sql = "update m_users_auth set flag = ? ,update_by = ? ,update_dt = getdate() ,row_version = row_version+1 "
					     +" where id=? and row_version  = ? ";
					
					log.info(sql);
					log.info(">flag="+srp.getAuthFlag());
					log.info(">update_by="+updateBy);
					log.info(">authId="+srp.getAuthId());
					log.info(">rowVersion="+srp.getRowVersion());
					
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, srp.getAuthFlag());
					pstmt.setString(2, updateBy);
					pstmt.setString(3, srp.getAuthId());					
					pstmt.setInt(4, srp.getRowVersion());
					res = pstmt.executeUpdate();
					if(res < 1){
						log.error("Concurrent Data Access. M_USERS_AUTH [id="+srp.getAuthId()+"]");
					}
				}

			}
			
		}catch(Exception e){
			log.error(e.getMessage());
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}

		}
		return res;
	}
	
	public boolean insertMUsersAuth(MUsersAuth model, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		String sql = null;
		boolean result = false;
		
		try{
			sql = "insert into m_users_auth "
				 +" (id "
				 +" ,menu_id "
				 +" ,role_id "
				 +" ,flag "
				 +" ,active_status "
				 +" ,create_by "
				 +" ,create_dt "
				 +" ,update_by "
				 +" ,update_dt ,row_version ) "
				 +" values(?,?,?,?,?,?,sysdatetime(),?,sysdatetime(),'1')";
			
			System.out.println(sql);
			System.out.println(">id:"+model.getId());
			System.out.println(">menuId:"+model.getMenuId());
			System.out.println(">roleId:"+model.getRoleId());
			System.out.println(">flag:"+model.getFlag());
			System.out.println(">activeStatus:"+model.getActiveStatus());
			
		
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, model.getId());
			pstmt.setString(2, model.getMenuId());
			pstmt.setString(3, model.getRoleId());
			pstmt.setInt(4, model.getFlag());
			pstmt.setString(5, model.getActiveStatus());
			pstmt.setString(6, model.getCreateBy());
			pstmt.setString(7, model.getUpdateBy());
			result = pstmt.execute();

			
		}catch(Exception e){
			log.error(e.getMessage());
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return result;
	}
	
	public SetupRoleProfileBO getRoleList(SetupRoleProfileBO bo, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		List<MRole> resultList = null;
		MRole mr = null;
		
		try{
			sql = "select * from m_role";
			
			System.out.println(sql);
			
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			if(rs != null){
				resultList = new ArrayList<MRole>();
				
				while(rs.next()){
					mr = new MRole();
					mr.setId(rs.getString("id"));
					mr.setCode(rs.getString("code"));
					mr.setName(rs.getString("name"));
					mr.setDescription(rs.getString("description"));
					mr.setActiveStatus(rs.getString("active_status"));

					resultList.add(mr);
				}
				
				bo.setRoleList(resultList);
			}
			
			
		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return bo;
	}
	
}

class SeqShowComparator implements Comparator<SetupRoleProfileBO>{

	public int compare(SetupRoleProfileBO o1, SetupRoleProfileBO o2) {
		return Integer.parseInt(o1.getSeqShow())>Integer.parseInt(o2.getSeqShow())?1:0;
	}
	
}